package com.cqeec.myblog.dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.cqeec.myblog.model.Article;
import com.cqeec.myblog.util.DbUtil;
import com.cqeec.myblog.util.PageBean;

/**
 * 针对文章表进行读写数据的接口的实现类。
 * 
 * @author TLF。
 *
 */
public class ArticleDaoImpl implements ArticleDao {

	@Override
	public List<Article> select() {
		List<Article> list = null;
		try {
			Connection conn = DbUtil.getDbConnection();
			Statement state = conn.createStatement();
			ResultSet rs = state.executeQuery("SELECT * FROM Article");
			
			
			// 遍历数据结果集将数据结果中的每条记录转为Java类对象(Model)
			list = new ArrayList<Article>();
			Article article;
			while (rs.next()) {
				article = new Article(); // 一条记录对应一个对象
				// 将记录中字段的值对应的存入对象属性中
				article.setId(rs.getString("id"));
				article.setBlogger(rs.getString("blogger"));
				article.setTitle(rs.getString("title"));
				article.setContent(rs.getString("content"));
				article.setCreationTime(rs.getDate("creationTime"));
				article.setIsReleased(rs.getBoolean("isReleased"));
				article.setReleaseTime(rs.getDate("releaseTime"));
				article.setCategoryId(rs.getString("categoryId"));
				list.add(article); // 将对象添加列表中
			}
			rs.close();
			state.close();
		} catch (Exception e) {
			list = null;
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public void deleteArticleById(String articleId)  {
		try {
			Connection conn = DbUtil.getDbConnection();
			PreparedStatement	ps = conn.prepareStatement("delete from Article where id=?");
			ps.setString(1, articleId);
			ps.execute();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	@Override
	public void createArticle(Article article) {
		try {
			Connection conn = DbUtil.getDbConnection();
			PreparedStatement	ps = conn.prepareStatement("insert into Article(id,title,content,blogger,creationTime,isReleased,categoryId)values(?,?,?,?,?,?,?)");
			ps.setString(1, article.getId());
			ps.setString(2, article.getTitle());
			ps.setString(3, article.getContent());
			ps.setString(4, article.getBlogger());
			ps.setDate(5, new Date(article.getCreationTime().getTime()));
			ps.setBoolean(6, false);
			ps.setString(7, article.getCategoryId());
			ps.execute();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	@Override
	public void updateById(String id, String title, String content) {
		// TODO Auto-generated method stub
		try {
			Connection conn = DbUtil.getDbConnection();
			PreparedStatement	ps = conn.prepareStatement("update article set title=?,content=? where id=? ");
			ps.setString(1, title);
			ps.setString(2, content);
			ps.setString(3, id);
			ps.execute();
		} catch (Exception e) {
			// TODO: handle exception
		}
	}

	@Override
	public List<Article> selectByCidAndCurPage(String cid,PageBean<Article> pageBean) {
		
		
		try {
			Connection conn = DbUtil.getDbConnection();
			if(cid==null||cid.equals("")) {
				PreparedStatement	ps = conn.prepareStatement("SELECT * FROM Article limit ?,?");
				ps.setInt(1,(pageBean.getCurPage()-1)*pageBean.getPageSize());
				ps.setInt(2, pageBean.getCurPage()*pageBean.getPageSize());
				ResultSet rs=ps.executeQuery();
				List<Article> list=new ArrayList<>();
				while(rs.next()) {
					Article article = new Article(); // 一条记录对应一个对象
					// 将记录中字段的值对应的存入对象属性中
					article.setId(rs.getString("id"));
					article.setBlogger(rs.getString("blogger"));
					article.setTitle(rs.getString("title"));
					article.setContent(rs.getString("content"));
					article.setCreationTime(rs.getDate("creationTime"));
					article.setIsReleased(rs.getBoolean("isReleased"));
					article.setReleaseTime(rs.getDate("releaseTime"));
					article.setCategoryId(rs.getString("cid"));
					list.add(article); // 将对象添加列表中
				}
				return list;
			}else {
				PreparedStatement	ps = conn.prepareStatement("SELECT * FROM Article where cid=? limit ?,?");
				ps.setString(1,cid);
				ps.setInt(2,(pageBean.getCurPage()-1)*pageBean.getPageSize());
				ps.setInt(3, pageBean.getCurPage()*pageBean.getPageSize());
				ResultSet rs=ps.executeQuery();
				List<Article> list=new ArrayList<>();
				while(rs.next()) {
					Article article = new Article(); // 一条记录对应一个对象
					// 将记录中字段的值对应的存入对象属性中
					article.setId(rs.getString("id"));
					article.setBlogger(rs.getString("blogger"));
					article.setTitle(rs.getString("title"));
					article.setContent(rs.getString("content"));
					article.setCreationTime(rs.getDate("creationTime"));
					article.setIsReleased(rs.getBoolean("isReleased"));
					article.setReleaseTime(rs.getDate("releaseTime"));
					article.setCategoryId(rs.getString("cid"));
					list.add(article); // 将对象添加列表中
				}
				return list;
			}
			
			
		} catch (Exception e) {
			return null;
			
		}
		
		
		
		
		
	}

	@Override
	public int selectCountByCid(String cid) {
		try {
			Connection conn = DbUtil.getDbConnection();
			String sql="";
			if(cid!=null&&!"".equals(cid))sql=" where cid="+"'"+cid+"'";
			PreparedStatement ps=conn.prepareStatement("select count(*) from article"+sql);
			ResultSet rs=ps.executeQuery();
			rs.next();
			return rs.getInt(1);
		} catch (Exception e) {
		}
		return 0;
	}

}
